package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import constants.Constants;
import domain.repro.ReproMisc;
import domain.repro.SeasonalityRepro;

public class ReproDao {
	FileDao fileDao = new FileDao();

	String dbUrl = Constants.DB_URL;
	String dbUsername = Constants.DB_USERNAME;
	String dbPassword = Constants.DB_PASSWORD;

	public SeasonalityRepro getSeasonalityConc(int fileNo){
		SeasonalityRepro result = new SeasonalityRepro();
		String query = "select Abs_Adjustment, Seasonality, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec from dbo.DVIPV_REPRO_CONC_SEASONALITY where file_no = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				result.setAbsAdjustment(rs.getDouble("Abs_Adjustment"));
				result.setOverallSeasonality(rs.getDouble("Seasonality"));
				
				for (int i=1;i<=12;i++){
					result.getMonthlySeasonality()[i] = rs.getDouble(Constants.MONTHS[i]);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public SeasonalityRepro getSeasonalityHeat(int fileNo){
		SeasonalityRepro result = new SeasonalityRepro();
		String query = "select Abs_Adjustment, Seasonality, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec from dbo.DVIPV_REPRO_HEAT_SEASONALITY where file_no = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				result.setAbsAdjustment(rs.getDouble("Abs_Adjustment"));
				result.setOverallSeasonality(rs.getDouble("Seasonality"));
				
				for (int i=1;i<=12;i++){
					result.getMonthlySeasonality()[i] = rs.getDouble(Constants.MONTHS[i]);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getAbortRisk(int fileNo){
		double[] result = new double[Constants.MPREG+1];
		String query = "select Preg_Month2, Preg_Month3, Preg_Month4, Preg_Month5, Preg_Month6, Preg_Month7, Preg_Month8 from dbo.DVIPV_REPRO_ABORT_RISK_MPREG where file_no = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				for(int i=2;i<=Constants.MPREG-1;i++){
					result[i] = rs.getDouble("Preg_Month"+i);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getConcRisk(int fileNo){
		double[] result = new double[Constants.LACT+1];
		String query = "select Lact1, Lact2, Lact3, Lact4, Lact5, Lact6, Lact7, Lact8, Lact9, Lact10, Lact11, Lact12 from dbo.DVIPV_REPRO_CONC_RISK_LACT where file_no = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				for(int i=1;i<=Constants.LACT;i++){
					result[i] = rs.getDouble("Lact"+i);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getPctConcMim(int fileNo){
		double[] result = new double[Constants.MIM+1];
		String query = "select MIM3, MIM4, MIM5, MIM6, MIM7, MIM8, MIM9, MIM10, MIM11, MIM12, MIM13, MIM14, MIM15, MIM16 from dbo.DVIPV_REPRO_CONC_PCT_MIM where file_no = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				for(int i=3;i<=16;i++){
					result[i] = rs.getDouble("MIM"+i);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getPctHeatMim(int fileNo){
		double[] result = new double[Constants.MIM+1];
		String query = "select MIM3, MIM4, MIM5, MIM6, MIM7, MIM8, MIM9, MIM10, MIM11, MIM12, MIM13, MIM14, MIM15, MIM16 from dbo.DVIPV_REPRO_HEAT_PCT_MIM where file_no = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				for(int i=3;i<=16;i++){
					result[i] = rs.getDouble("MIM"+i);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public ReproMisc getReproMisc(int fileNo){
		ReproMisc result = new ReproMisc();
		String query = "select vwp_1, vwp_2, vwp_3, lbm, TimeBreeding, P_Semen, MaxLact from dbo.DVIPV_REPRO_MISC where file_no = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				result.getVwp()[1] = rs.getInt("vwp_1");
				result.getVwp()[2] = rs.getInt("vwp_2");
				result.getVwp()[3] = rs.getInt("vwp_3");
				result.setLbm(rs.getInt("lbm"));
				result.setTimeBreeding(rs.getDouble("TimeBreeding"));
				result.setP_semen(rs.getDouble("P_Semen"));
				result.setMaxLact(rs.getInt("MaxLact"));
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public boolean updateSeasonalityConc(int fileNo, SeasonalityRepro seaConc){
		boolean result = false;
		String query = "update dbo.DVIP_IN_REPRO_CONC_SEASONALITY set Abs_Adjustment = ?, Seasonality = ?, Jan = ?, Feb = ?, Mar = ?, Apr = ?, May = ?, Jun = ?, Jul = ?, Aug = ?, Sep = ?, Oct = ?, Nov = ?, Dec = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			pStmt.setDouble(colIndex++, seaConc.getAbsAdjustment());
			pStmt.setDouble(colIndex++, seaConc.getOverallSeasonality());
			
			for(int sea=1; sea<=12; sea++){
				pStmt.setDouble(colIndex++, seaConc.getMonthlySeasonality()[sea]);	
			}
			
			pStmt.setInt(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean updateSeasonalityHeat(int fileNo, SeasonalityRepro seaHeat){
		boolean result = false;
		String query = "update dbo.DVIP_IN_REPRO_HEAT_SEASONALITY set Abs_Adjustment = ?, Seasonality = ?, Jan = ?, Feb = ?, Mar = ?, Apr = ?, May = ?, Jun = ?, Jul = ?, Aug = ?, Sep = ?, Oct = ?, Nov = ?, Dec = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			pStmt.setDouble(colIndex++, seaHeat.getAbsAdjustment());
			pStmt.setDouble(colIndex++, seaHeat.getOverallSeasonality());
			
			for(int sea=1; sea<=12; sea++){
				pStmt.setDouble(colIndex++, seaHeat.getMonthlySeasonality()[sea]);	
			}
			
			pStmt.setInt(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean updateAbortRisk(int fileNo, double[] abortRisk){
		boolean result = false;
		String query = "update dbo.DVIP_IN_REPRO_ABORT_RISK_MPREG set Preg_Month2 = ?, Preg_Month3 = ?, Preg_Month4 = ?, Preg_Month5 = ?, Preg_Month6 = ?, Preg_Month7 = ?, Preg_Month8 = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			
			for(int month=2; month<=8; month++){
				pStmt.setDouble(colIndex++, abortRisk[month]);	
			}
			
			pStmt.setInt(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean updateConcRisk(int fileNo, double[] concRisk){
		boolean result = false;
		String query = "update dbo.DVIP_IN_REPRO_CONC_RISK_LACT set Lact1 = ?, Lact2 = ?, Lact3 = ?, Lact4 = ?, Lact5 = ?, Lact6 = ?, Lact7 = ?, Lact8 = ?, Lact9 = ?, Lact10 = ?, Lact11 = ?, Lact12 = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			
			for(int lact=1; lact<=12; lact++){
				pStmt.setDouble(colIndex++, concRisk[lact]);	
			}
			
			pStmt.setInt(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean updatePctConcMim(int fileNo, double[] pctConcMim){
		boolean result = false;
		String query = "update dbo.DVIP_IN_REPRO_CONC_PCT_MIM set MIM3 = ?, MIM4 = ?, MIM5 = ?, MIM6 = ?, MIM7 = ?, MIM8 = ?, MIM9 = ?, MIM10 = ?, MIM11 = ?, MIM12 = ?, MIM13 = ?, MIM14 = ?, MIM15 = ?, MIM16 = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			
			for(int mim=3; mim<=16; mim++){
				pStmt.setDouble(colIndex++, pctConcMim[mim]);	
			}
			
			pStmt.setInt(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean updatePctHeatMim(int fileNo, double[] pctHeatMim){
		boolean result = false;
		String query = "update dbo.DVIP_IN_REPRO_HEAT_PCT_MIM set MIM3 = ?, MIM4 = ?, MIM5 = ?, MIM6 = ?, MIM7 = ?, MIM8 = ?, MIM9 = ?, MIM10 = ?, MIM11 = ?, MIM12 = ?, MIM13 = ?, MIM14 = ?, MIM15 = ?, MIM16 = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			
			for(int mim=3; mim<=16; mim++){
				pStmt.setDouble(colIndex++, pctHeatMim[mim]);	
			}
			
			pStmt.setInt(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean updateReproMisc(int fileNo, ReproMisc reproMisc){
		boolean result = false;
		String query = "update dbo.DVIP_IN_REPRO_MISC set P_Semen = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			
			pStmt.setDouble(colIndex++, reproMisc.getP_semen());	
			
			pStmt.setInt(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
}
